import pymysql,xlwt

def export_excel(db_name):

    book = xlwt.Workbook()#新建一个excel
    sheet = book.add_sheet('students')#新建一个sheet页

    host = '118.24.3.40'  # ip
    user = 'jxz'  # 用户名
    password = '123456'  # 密码
    db = 'jxz'  # 数据库名
    port = 3306  # 端口号

    connect = pymysql.connect(host=host, user=user,
                                  password=password,
                                  port=port, db=db,
                                  autocommit=True  # 自动提交
                                  )  # 连接数据库
    cur = connect.cursor()  # 建立游标
    cur.execute('select * from '+ db_name + ' limit 1')
    description = [descr[0] for descr in list(cur.description)] #获取表key
    for col,key in enumerate(description):#在excel写入数据库表的key
        sheet.write(0, col, key)

    cur.execute('select * from ' + db_name)#查询students表
    result = cur.fetchall()#保存查询出的结果
    data = [data for data in result]#逐条打印查询出的结果

    for row,i in enumerate(data): #在excel写入表的value
        for col,value in enumerate(i):
            sheet.write(row+1,col,value)


    cur.close()  # 关闭数据库
    connect.close()  # 关闭数据库连接
    book.save('students.xls')
export_excel('students')